package dao;

import beans.Account;
import helper.ReadConfig;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class CheckAccount {

    public Account checkLogin(Account account, Connection con) {
        try {
            String sSQL = "select * from tbl_Account where Acc_UserName = '" + account.getUser_name()
                    + "' and Acc_PassWord = '" + account.getPass_word() + "'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                account.setRoll(rs.getString("Roll"));
                account.setAcc_id(rs.getString("Acc_id"));
                account.setAddress(rs.getString("Acc_Address"));
                account.setEmail(rs.getString("Acc_Email"));
                account.setFull_name(rs.getString("Acc_FullName"));
                account.setGender(rs.getString("Acc_Gender"));
                account.setPhone(rs.getString("Acc_Phone"));
            }
            rs.close();
            stm.close();
            return account;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean createAccount(Account account) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "insert into tbl_Account(Acc_UserName, Acc_PassWord, "
                    + " Acc_FullName, Acc_Gender, Acc_Address, Acc_Phone, Acc_Email, Roll) "
                    + " values('" + account.getUser_name() + "','"
                    + account.getPass_word() + "','" + account.getFull_name() + "',"
                    + Integer.parseInt(account.getGender()) + ",'" + account.getAddress() + "','"
                    + account.getPhone() + "','" + account.getEmail() + "','" + account.getRoll()
                    + "')";

            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateAccount(Account account) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "Update tbl_Account set "
                    + "   Acc_UserName='" + account.getUser_name()
                    + "', Acc_PassWord='" + account.getPass_word()
                    + "', Acc_FullName='" + account.getFull_name()
                    + "', Acc_Gender=" + Integer.parseInt(account.getGender())
                    + " , Acc_Address='" + account.getAddress()
                    + "', Acc_Phone='" + account.getPhone()
                    + "', Acc_Email='" + account.getEmail()
                    + "', Roll='" + account.getRoll()
                    + "' WHERE Acc_Id = " + Integer.parseInt(account.getAcc_id());

            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteAccount(String acc_id, String roll) {
        try {
            if (roll.equals("staff")) {
                MngClass mngClass = new MngClass();
                mngClass.deleteClassByAccId(acc_id);
            }
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "DELETE FROM tbl_Account WHERE Acc_Id = " + Integer.parseInt(acc_id);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Account> showAccount() {
        try {
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account ";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Account account = new Account();
                account.setAcc_id(rs.getString("Acc_id"));
                account.setUser_name(rs.getString("Acc_UserName"));
                account.setPass_word(rs.getString("Acc_PassWord"));
                account.setAddress(rs.getString("Acc_Address"));
                account.setEmail(rs.getString("Acc_Email"));
                account.setFull_name(rs.getString("Acc_FullName"));
                account.setGender(rs.getString("Acc_Gender"));
                account.setPhone(rs.getString("Acc_Phone"));
                account.setRoll(rs.getString("Roll"));
                listAcc.add(account);
            }
            rs.close();
            stm.close();
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public String getFullName(String acc_id) {
        try {
            String full_name = "";
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account Where Acc_Id = " + Integer.parseInt(acc_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                full_name = rs.getString("Acc_FullName");
            }
            rs.close();
            stm.close();
            return full_name;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> listAccByRoll(String roll) {
        try {
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Roll = '" + roll + "'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Account account = new Account();
                account.setAcc_id(rs.getString("Acc_id"));
                account.setUser_name(rs.getString("Acc_UserName"));
                account.setPass_word(rs.getString("Acc_PassWord"));
                account.setAddress(rs.getString("Acc_Address"));
                account.setEmail(rs.getString("Acc_Email"));
                account.setFull_name(rs.getString("Acc_FullName"));
                account.setGender(rs.getString("Acc_Gender"));
                account.setPhone(rs.getString("Acc_Phone"));
                account.setRoll(rs.getString("Roll"));
                listAcc.add(account);
            }
            rs.close();
            stm.close();
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public String checkAccId(String acc_id) {
        try {
            String roll = "";
            String sSQL = "select Roll from tbl_Account where Acc_Id = " + acc_id;
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                roll = rs.getString("Roll");
            }
            rs.close();
            stm.close();
            return roll;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> listAccByClassId(String class_id) {
        try {
            Student st = new Student();
            List<String> listSt = st.listStudentByClass(class_id);
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            for (String acc_id : listSt) {
                String sSQL = "select * from tbl_Account where Acc_Id = " + Integer.parseInt(acc_id);
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(sSQL);
                if (rs.next()) {
                    Account account = new Account();
                    account.setAcc_id(rs.getString("Acc_id"));
                    account.setUser_name(rs.getString("Acc_UserName"));
                    account.setPass_word(rs.getString("Acc_PassWord"));
                    account.setAddress(rs.getString("Acc_Address"));
                    account.setEmail(rs.getString("Acc_Email"));
                    account.setFull_name(rs.getString("Acc_FullName"));
                    account.setGender(rs.getString("Acc_Gender"));
                    account.setPhone(rs.getString("Acc_Phone"));
                    account.setRoll(rs.getString("Roll"));
                    listAcc.add(account);
                }
                rs.close();
                stm.close();
            }
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean changePassword(Account account) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "Update tbl_Account set "
                    + " Acc_PassWord='" + account.getPass_word()
                    + "' WHERE Acc_Id = " + Integer.parseInt(account.getAcc_id());
            System.out.println(sSQL);
            Statement stm = con.createStatement();
            int rs = stm.executeUpdate(sSQL);
            System.out.println(rs);
            if (rs == 1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean checkUsername(String username, String acc_id) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Acc_UserName = '" + username
                    + "' and Acc_Id != " + Integer.parseInt(acc_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                return true;
            }
            rs.close();
            stm.close();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean checkUsername(String username) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Acc_UserName = '" + username + "'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                return true;
            }
            rs.close();
            stm.close();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean checkPassword(String acc_id, String password) {
        try {
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Acc_PassWord = '" + password
                    + "' and Acc_Id =" + Integer.parseInt(acc_id);
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            if (rs.next()) {
                return true;
            }
            rs.close();
            stm.close();
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Account> searchAccByFullName(String search_full_name) {
        try {
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Acc_FullName like '%" + search_full_name + "%'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Account account = new Account();
                account.setAcc_id(rs.getString("Acc_id"));
                account.setUser_name(rs.getString("Acc_UserName"));
                account.setPass_word(rs.getString("Acc_PassWord"));
                account.setAddress(rs.getString("Acc_Address"));
                account.setEmail(rs.getString("Acc_Email"));
                account.setFull_name(rs.getString("Acc_FullName"));
                account.setGender(rs.getString("Acc_Gender"));
                account.setPhone(rs.getString("Acc_Phone"));
                account.setRoll(rs.getString("Roll"));
                listAcc.add(account);
            }
            rs.close();
            stm.close();
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> searchAccByUserName(String search_user_name) {
        try {
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Acc_UserName like '%" + search_user_name + "%'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Account account = new Account();
                account.setAcc_id(rs.getString("Acc_id"));
                account.setUser_name(rs.getString("Acc_UserName"));
                account.setPass_word(rs.getString("Acc_PassWord"));
                account.setAddress(rs.getString("Acc_Address"));
                account.setEmail(rs.getString("Acc_Email"));
                account.setFull_name(rs.getString("Acc_FullName"));
                account.setGender(rs.getString("Acc_Gender"));
                account.setPhone(rs.getString("Acc_Phone"));
                account.setRoll(rs.getString("Roll"));
                listAcc.add(account);
            }
            rs.close();
            stm.close();
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> searchAccByEmail(String search_email) {
        try {
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Acc_Email like '%" + search_email + "%'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Account account = new Account();
                account.setAcc_id(rs.getString("Acc_id"));
                account.setUser_name(rs.getString("Acc_UserName"));
                account.setPass_word(rs.getString("Acc_PassWord"));
                account.setAddress(rs.getString("Acc_Address"));
                account.setEmail(rs.getString("Acc_Email"));
                account.setFull_name(rs.getString("Acc_FullName"));
                account.setGender(rs.getString("Acc_Gender"));
                account.setPhone(rs.getString("Acc_Phone"));
                account.setRoll(rs.getString("Roll"));
                listAcc.add(account);
            }
            rs.close();
            stm.close();
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> searchAccByAddress(String search_address) {
        try {
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            String sSQL = "select * from tbl_Account where Acc_Address like '%" + search_address + "%'";
            Statement stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sSQL);
            while (rs.next()) {
                Account account = new Account();
                account.setAcc_id(rs.getString("Acc_id"));
                account.setUser_name(rs.getString("Acc_UserName"));
                account.setPass_word(rs.getString("Acc_PassWord"));
                account.setAddress(rs.getString("Acc_Address"));
                account.setEmail(rs.getString("Acc_Email"));
                account.setFull_name(rs.getString("Acc_FullName"));
                account.setGender(rs.getString("Acc_Gender"));
                account.setPhone(rs.getString("Acc_Phone"));
                account.setRoll(rs.getString("Roll"));
                listAcc.add(account);
            }
            rs.close();
            stm.close();
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> searchAccByFullNameAndClass(String search_full_name, String class_id) {
        try {
            Student st = new Student();
            List<String> listSt = st.listStudentByClass(class_id);
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            for (String acc_id : listSt) {
                String sSQL = "select * from tbl_Account where Acc_FullName like '%" + search_full_name
                        + "%' and Acc_Id = " + Integer.parseInt(acc_id);
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(sSQL);
                while (rs.next()) {
                    Account account = new Account();
                    account.setAcc_id(rs.getString("Acc_id"));
                    account.setUser_name(rs.getString("Acc_UserName"));
                    account.setPass_word(rs.getString("Acc_PassWord"));
                    account.setAddress(rs.getString("Acc_Address"));
                    account.setEmail(rs.getString("Acc_Email"));
                    account.setFull_name(rs.getString("Acc_FullName"));
                    account.setGender(rs.getString("Acc_Gender"));
                    account.setPhone(rs.getString("Acc_Phone"));
                    account.setRoll(rs.getString("Roll"));
                    listAcc.add(account);
                }
                rs.close();
                stm.close();
            }
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> searchAccByUserNameAndClass(String search_user_name, String class_id) {
        try {
            Student st = new Student();
            List<String> listSt = st.listStudentByClass(class_id);
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            for (String acc_id : listSt) {
                String sSQL = "select * from tbl_Account where Acc_UserName like '%" + search_user_name
                        + "%' and Acc_Id = " + Integer.parseInt(acc_id);
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(sSQL);
                while (rs.next()) {
                    Account account = new Account();
                    account.setAcc_id(rs.getString("Acc_id"));
                    account.setUser_name(rs.getString("Acc_UserName"));
                    account.setPass_word(rs.getString("Acc_PassWord"));
                    account.setAddress(rs.getString("Acc_Address"));
                    account.setEmail(rs.getString("Acc_Email"));
                    account.setFull_name(rs.getString("Acc_FullName"));
                    account.setGender(rs.getString("Acc_Gender"));
                    account.setPhone(rs.getString("Acc_Phone"));
                    account.setRoll(rs.getString("Roll"));
                    listAcc.add(account);
                }
                rs.close();
                stm.close();
            }
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> searchAccByEmailAndClass(String search_email, String class_id) {
        try {
            Student st = new Student();
            List<String> listSt = st.listStudentByClass(class_id);
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            for (String acc_id : listSt) {
                String sSQL = "select * from tbl_Account where Acc_Email like '%" + search_email
                        + "%' and Acc_Id = " + Integer.parseInt(acc_id);
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(sSQL);
                while (rs.next()) {
                    Account account = new Account();
                    account.setAcc_id(rs.getString("Acc_id"));
                    account.setUser_name(rs.getString("Acc_UserName"));
                    account.setPass_word(rs.getString("Acc_PassWord"));
                    account.setAddress(rs.getString("Acc_Address"));
                    account.setEmail(rs.getString("Acc_Email"));
                    account.setFull_name(rs.getString("Acc_FullName"));
                    account.setGender(rs.getString("Acc_Gender"));
                    account.setPhone(rs.getString("Acc_Phone"));
                    account.setRoll(rs.getString("Roll"));
                    listAcc.add(account);
                }
                rs.close();
                stm.close();
            }
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Account> searchAccByAddressAndClass(String search_address, String class_id) {
        try {
            Student st = new Student();
            List<String> listSt = st.listStudentByClass(class_id);
            List<Account> listAcc = new ArrayList<Account>();
            ReadConfig readConf = new ReadConfig();
            Connection con = readConf.getDBConnection();
            for (String acc_id : listSt) {
                String sSQL = "select * from tbl_Account where Acc_Address like '%" + search_address
                        + "%' and Acc_Id = " + Integer.parseInt(acc_id);
                Statement stm = con.createStatement();
                ResultSet rs = stm.executeQuery(sSQL);
                while (rs.next()) {
                    Account account = new Account();
                    account.setAcc_id(rs.getString("Acc_id"));
                    account.setUser_name(rs.getString("Acc_UserName"));
                    account.setPass_word(rs.getString("Acc_PassWord"));
                    account.setAddress(rs.getString("Acc_Address"));
                    account.setEmail(rs.getString("Acc_Email"));
                    account.setFull_name(rs.getString("Acc_FullName"));
                    account.setGender(rs.getString("Acc_Gender"));
                    account.setPhone(rs.getString("Acc_Phone"));
                    account.setRoll(rs.getString("Roll"));
                    listAcc.add(account);
                }
                rs.close();
                stm.close();
            }
            return listAcc;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
